import java.sql.*;
import java.util.Scanner;

public class demo {
    private static String url = "jdbc:mysql://localhost:3306/XK?Encodeing=utf8&useSSL=false";
    private static String user = "root";
    private static String password = "123456";
    private static Scanner scanner = new Scanner(System.in);
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url,user,password);
            while (true) {
                menu();
                int choice = scanner.nextInt();
                scanner.nextLine();

                switch (choice) {
                    case 1:
                        showStudent(connection);
                        break;
                    case 2:
                        findStudent(connection);
                        break;
                    case 3:
                        updateStudent(connection);
                        break;
                    case 4:
                        addStudent(connection);
                        break;
                    case 5:
                        deleteStudent(connection);
                        break;
                    case 6:
                        System.exit(0);
                }
            }
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            connection.close();
        }
    }

    private static void deleteStudent(Connection connection) throws SQLException {
        System.out.println("请输入要删除的学生的 id：");
        int id = scanner.nextInt();
        scanner.nextLine();
        if (!existStudent(connection,id)) {
            System.out.println("未找到该学生或表为空");
            return;
        }
        String sql = "delete from student where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            System.out.println("删除成功");
        }
        else {
            System.out.println("删除失败");
        }
    }

    private static void addStudent(Connection connection) throws SQLException {
        System.out.println("请输入学生信息：");
        System.out.println("ID：");
        int id = scanner.nextInt();
        scanner.nextLine();
        if (existStudent(connection,id)) {
            System.out.println("该学生已存在");
            return;
        }
        System.out.println("姓名：");
        String name = scanner.nextLine();
        System.out.println("年龄：");
        int age = scanner.nextInt();
        scanner.nextLine();
        System.out.println("性别：");
        String gender = scanner.nextLine();
        System.out.println("年级：");
        String grade = scanner.nextLine();
        String sql = "insert into student(id,name,age,gender,grade) values(?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,name);
            preparedStatement.setInt(3,age);
            preparedStatement.setString(4,gender);
            preparedStatement.setString(5,grade);
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("添加成功");
            }   else {
                System.out.println("添加失败");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
     }
    private static void updateStudent(Connection connection) throws SQLException {
        System.out.println("请输入要更新的学生的 id：");
        int id = scanner.nextInt();
        scanner.nextLine();
        if (!existStudent(connection,id)) {
            System.out.println("未找到该学生或表为空");
            return;
        }
        System.out.println("请输入新的学生信息：");
        System.out.println("姓名：");
        String name = scanner.nextLine();
        System.out.println("年龄：");
        int age = scanner.nextInt();
        scanner.nextLine();
        System.out.println("性别：");
        String gender = scanner.nextLine();
        System.out.println("年级：");
        String grade = scanner.nextLine();
        String sql = "update student set name = ?, age = ?, gender = ?, grade = ? where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,name);
        preparedStatement.setInt(2,age);
        preparedStatement.setString(3,gender);
        preparedStatement.setString(4,grade);
        preparedStatement.setInt(5,id);
        int rowsAffected = preparedStatement.executeUpdate();
        if (rowsAffected > 0) {
            System.out.println("更新成功");
        }
        else {
            System.out.println("更新失败");
        }
    }

    private static void findStudent(Connection connection) throws SQLException {
        System.out.println("请输入要查找的学生的 id：");
        int id = scanner.nextInt();
        scanner.nextLine();
        String sql = "select * from student where id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            System.out.println("找到学生:");
            System.out.println("ID: " + resultSet.getInt("id"));
            System.out.println("姓名: " + resultSet.getString("name"));
            System.out.println("年龄: " + resultSet.getInt("age"));
            System.out.println("性别: " + resultSet.getString("gender"));
            System.out.println("年级: " + resultSet.getString("grade"));
        } else {
            System.out.println("未找到该学生或表为空");
        }
    }

    private static void showStudent(Connection connection) throws SQLException {
        String sql = "select * from student";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        System.out.println("\tID\t姓名\t年龄\t性别\t年级");
        while (resultSet.next()) {
            System.out.printf("\t%d\t%s\t%d\t%s\t%s",resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getInt("age"),
                    resultSet.getString("gender"),
                    resultSet.getString("grade"));
            System.out.println();
        }
        resultSet.close();
    }

    private static boolean existStudent(Connection connection,int id) throws SQLException {
        String sql = "select * from student where id =?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet.next();
    }

    private static void menu() {
        System.out.println("__________菜单__________");
        System.out.println("Enter your choice");
        System.out.println("1、list all students");
        System.out.println("2、find student by its id");
        System.out.println("3、update a student");
        System.out.println("4、add a new student");
        System.out.println("5、delete an student by id");
        System.out.println("6、end program");
        System.out.println();
    }
}
